/******************************************************************************
** File		: Permission.sql
** Description	: Script for Setting Roles & Permissions from Stored Procedures, functions.
** Params	: None
** Returns	: None
** Author	: Vineet Batta
** Date		: 05/29/2008	
** ****************************************************************************/

	SET NOCOUNT ON
		IF NOT EXISTS (SELECT NAME FROM SYSUSERS WHERE NAME = 'ISRMExecuteObjects')
		BEGIN
			EXEC SP_ADDROLE [ISRMExecuteObjects] 
		END
GO

	DECLARE 
		 @RowNo   	INT
		,@Routine_Name  NVARCHAR(512)
		,@RowCount 	INT

----------------------------------------------------------------------------------------
-- Execution Permission from Stored procedures & User Defined Functions 
----------------------------------------------------------------------------------------

	DECLARE  @tblRoutine TABLE
	(
		RowId INT IDENTITY(1,1) PRIMARY KEY,
		Routine_Name Nvarchar(512)
	)
	
	INSERT INTO 
		@tblRoutine(Routine_Name)
	
	SELECT 
		Routine_Name 
	FROM 
		INFORMATION_SCHEMA.ROUTINES 
	WHERE 
		OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0 
		AND 
		OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), N'IsTableFunction') = 0
		AND 
		OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), N'IsInlineFunction') = 0
	
	SET 	@RowNo=1
	
	SELECT 	
		@RowCount=Count(RowID) 
	FROM 
		@tblRoutine
	
	WHILE	 @RowNo<=@RowCount
	
	BEGIN
		SELECT 
			@Routine_Name=Routine_Name 
		FROM 
			@tblRoutine 
		WHERE 	
			RowID=@RowNo
	
		EXEC('GRANT EXECUTE ON dbo.['+@Routine_Name+ '] TO [ISRMExecuteObjects]')
		SET @RowNo=@RowNo+1
	END
	
	----------------------------------------------------------------------------------------
	--  Select Permission for Inline & Table value Functions
	----------------------------------------------------------------------------------------
	DECLARE  @tblFunctions TABLE
	(
		RowId INT IDENTITY(1,1) PRIMARY KEY,
		Routine_Name Nvarchar(512)
	)
	
	INSERT INTO 
		@tblFunctions(Routine_Name)
	SELECT 
		Routine_Name 
	FROM 
		INFORMATION_SCHEMA.ROUTINES 
	WHERE 
		OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0 
		AND 
		(
			OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), N'IsTableFunction') = 1
			OR 
			OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), N'IsInlineFunction') = 1
		)
	
	SET 	@RowNo=1
	
	SELECT 	
		@RowCount=Count(RowID) 
	FROM 
		@tblFunctions
	
	WHILE	 @RowNo<=@RowCount
	
	BEGIN
		SELECT 
			@Routine_Name=Routine_Name 
		FROM 
			@tblFunctions 
		WHERE 	
			RowID=@RowNo
	
		EXEC('GRANT SELECT ON dbo.['+@Routine_Name+ '] TO [ISRMExecuteObjects]')
		SET @RowNo=@RowNo+1
	END

----------------------------------------------------
--Explicit for table
----------------------------------------------------
----------------------------------------------------
--Explicit for table
----------------------------------------------------
GO
GRANT SELECT ON dbo.[Activity]  TO [ISRMExecuteObjects]
GRANT SELECT ON dbo.[AuditTrail]  TO [ISRMExecuteObjects]
GRANT SELECT ON dbo.[PortalUsers]  TO [ISRMExecuteObjects]
GRANT SELECT ON dbo.[Widget] TO [ISRMExecuteObjects]
GRANT SELECT ON dbo.[WidgetInstance]  TO [ISRMExecuteObjects]

GO
Print 'Setting Permission for Objects Process has Completed'

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'$(UserName)')
DROP LOGIN [$(UserName)]
GO

CREATE LOGIN [$(UserName)] WITH PASSWORD=N'$(UserPWD)', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE USER [$(UserName)] FOR LOGIN [$(UserName)] WITH DEFAULT_SCHEMA=[ISRMExecuteObjects]
GO

EXECUTE sp_addrolemember N'ISRMExecuteObjects', N'$(UserName)'
GO

